﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using OSISGradeLevelBusinessLayer;
using OpenSISDataConnection;
using System.Globalization;

namespace OSISGradeLeveDataLayer
{
    /// <summary>
    /// Summary description for GradeLevelDL
    /// </summary>
    public class GradeLevelDL
    {
        public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];
       // public static string server = "MSSQLServer";
        public GradeLevelDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public void AddGradeLevels(GradeLevelBL GradeLevel)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                DataSet dsShortOrder = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax grd_lvl_id,grade_levels");

                dsShortOrder = DataConnection.ConnectionPath.Getdata("select isnull(max(grd_lvl_cd),0) + 1 as short_order from grade_levels where sch_id='" + GradeLevel.School_Id + "'");

                String strAddGradeLevel = "INSERT into grade_levels(grd_lvl_id,grd_lvl_cd,grd_lvl_short_nm,grd_lvl_nm,grd_lvl_id_next,last_updated,grd_lvl_id_prev,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "',N'" + GradeLevel.GradeLevelShortName.Replace("'", "''") + "',N'" + GradeLevel.GradeLevelName.Replace("'", "''") + "'," + GradeLevel.GrdLevelIdNext + ",N'" + GradeLevel.LastUpdated + "'," + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + GradeLevel.School_Id + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strAddGradeLevel);


            }



            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('grd_lvl_id','grade_levels')");

                DateTime s = Convert.ToDateTime(GradeLevel.LastUpdated,DateTimeFormatInfo.InvariantInfo);
                GradeLevel.LastUpdated = s.ToString("yyyy/MM/dd");

                DataSet dsShortOrder = null;

                dsShortOrder = DataConnection.ConnectionPath.Getdata("select IFNULL(max(grd_lvl_cd),0) + 1 as short_order from grade_levels where sch_id='" + GradeLevel.School_Id + "'");


                String strAddGradeLevel = "INSERT into grade_levels(grd_lvl_id,grd_lvl_cd,grd_lvl_short_nm,grd_lvl_nm,grd_lvl_id_next,last_updated,grd_lvl_id_prev,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "',N'" + GradeLevel.GradeLevelShortName.Replace("'", "''") + "',N'" + GradeLevel.GradeLevelName.Replace("'", "''") + "'," + GradeLevel.GrdLevelIdNext + ",N'" + GradeLevel.LastUpdated + "'," + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + GradeLevel.School_Id + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strAddGradeLevel);


            }
            else
            { }


        }

        public System.Data.DataTable GradeLevelDetails(GradeLevelBL GradeLevel)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select grade_levels.grd_lvl_id,grade_levels.grd_lvl_cd,grade_levels.grd_lvl_short_nm,grade_levels.grd_lvl_nm,grade_levels.grd_lvl_id_next,GL.grd_lvl_nm as Next_grade from grade_levels left outer join grade_levels as GL on GL.grd_lvl_id=grade_levels.grd_lvl_id_next where grade_levels.sch_id=" + GradeLevel.School_Id + " order by grade_levels.grd_lvl_cd").Tables[0];
            return dt;
        }

        public DataSet PopulateNextGrade(GradeLevelBL GradeLevel)
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from grade_levels where sch_id=" + GradeLevel.School_Id + "");
            return ds;
        }

        public void DeleteGradeLevel(GradeLevelBL GradeLevel)
        {

            string strGradeLevelDelete = "delete from grade_levels where grd_lvl_id=" + GradeLevel.GrdLevelId + "";

            DataConnection.ConnectionPath.ExecuteCommand(strGradeLevelDelete);

        }

        public void UpdateGradeLevel(GradeLevelBL GradeLevel)
        {

            if (server == "MSSQLServer")
            {
                string strUpdateGradeLevel = "update grade_levels set grd_lvl_short_nm= N'" + GradeLevel.GradeLevelShortName.Replace("'", "''") + "',grd_lvl_nm= N'" + GradeLevel.GradeLevelName.Replace("'", "''") + "',grd_lvl_id_next=" + GradeLevel.GrdLevelIdNext + ",last_updated= N'" + GradeLevel.LastUpdated + "' where grd_lvl_id=" + GradeLevel.GrdLevelId + "";

                DataConnection.ConnectionPath.ExecuteCommand(strUpdateGradeLevel);
            }

            if (server == "MySQL")
            {
                DateTime s = Convert.ToDateTime(GradeLevel.LastUpdated, DateTimeFormatInfo.InvariantInfo);
                GradeLevel.LastUpdated = s.ToString("yyyy/MM/dd");


                string strUpdateGradeLevel = "update grade_levels set grd_lvl_short_nm= N'" + GradeLevel.GradeLevelShortName.Replace("'", "''") + "',grd_lvl_nm= N'" + GradeLevel.GradeLevelName.Replace("'", "''") + "',grd_lvl_id_next=" + GradeLevel.GrdLevelIdNext + ",last_updated= N'" + GradeLevel.LastUpdated + "' where grd_lvl_id=" + GradeLevel.GrdLevelId + "";

                DataConnection.ConnectionPath.ExecuteCommand(strUpdateGradeLevel);

            }
            else
            {
            }


        }

        public string CheckExistTitleAndShortName(GradeLevelBL GradeLevel)
        {
            DataTable dt = null;
            string RequireField = "";
            dt = DataConnection.ConnectionPath.Getdata("select count(*) from grade_levels where grd_lvl_nm= N'" + GradeLevel.GradeLevelName.Replace("'","''") + "' and sch_id="+ GradeLevel.School_Id +"").Tables[0];
            if (Convert.ToInt32(dt.Rows[0][0]) > 0)
            {
                RequireField = "T";
                return RequireField;
            }

            dt = DataConnection.ConnectionPath.Getdata("select count(*) from grade_levels where grd_lvl_short_nm= N'" + GradeLevel.GradeLevelShortName.Replace("'","''") + "' and sch_id=" + GradeLevel.School_Id + "").Tables[0];
            if (Convert.ToInt32(dt.Rows[0][0]) > 0)
            {
                RequireField = "S";
                return RequireField;
            }

            return RequireField;
        }
        public System.Data.DataTable CheckGradeLevelUse(GradeLevelBL GradeLevel)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select count(*) from schools where sch_min_grade_id=" + GradeLevel.GrdLevelId + " or sch_max_grade_id=" + GradeLevel.GrdLevelId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable CheckGradeLevelUseInNextGradeLevel(GradeLevelBL GradeLevel)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select count(*) from grade_levels where " + GradeLevel.GrdLevelId + " in(select grd_lvl_id_next from grade_levels)").Tables[0];
            return dt;
        }

        public void UpdateGradeLevelsShortOrder(GradeLevelBL GradeLevel)
        {

            string strUpdateGradeLevelOrder = "update grade_levels set grd_lvl_cd= N'" + GradeLevel.GradeLevelShortOrder.Replace("'", "''") + "' where grd_lvl_id=" + GradeLevel.GrdLevelId + "";

            DataConnection.ConnectionPath.ExecuteCommand(strUpdateGradeLevelOrder);

        }

        public System.Data.DataTable PopulateMaxGradeLevelID(GradeLevelBL GradeLevel)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("SELECT max(grd_lvl_id) from grade_levels where sch_id = "+GradeLevel.School_Id).Tables[0];
            return dt;
        }
      
    }
}
